package zyw;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * zyw.PoiWeekly
 *
 * @author byc
 * @date 2024-06-11
 */
public class PoiWeekly {

    public static void main(String[] args) throws IOException {
        //看这里~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//        //上周周报
//        String inputFilePathA = "C:\\D\\weeklysuffer\\old周报.xlsx";
//        //这周周报
//        String inputFilePathB = "C:\\D\\weeklysuffer\\new周报.xlsx";

        //上周周报
        String inputFilePathA = "D:\\work\\临时文件\\zyw\\周报\\old周报.xlsx";
        //这周周报
        String inputFilePathB = "D:\\work\\临时文件\\zyw\\周报\\new周报.xlsx";


        try(FileInputStream fisA = new FileInputStream(inputFilePathA);
            Workbook workbookA = new XSSFWorkbook(fisA);

            FileInputStream fisB = new FileInputStream(inputFilePathB);
            Workbook workbookB = new XSSFWorkbook(fisB)
        ){

            // 创建一个公式评估器
            FormulaEvaluator evaluatorA = workbookA.getCreationHelper().createFormulaEvaluator();
            FormulaEvaluator evaluatorB = workbookB.getCreationHelper().createFormulaEvaluator();
            //清除缓存重新评估
            evaluatorA.evaluateAll();
            evaluatorB.evaluateAll();

            Sheet sheetA = workbookA.getSheetAt(0);
            Sheet sheetB = workbookB.getSheetAt(0);

            for (int i = 3; i < 117; i++ ){
                Row rowA = sheetA.getRow(i);
                Row rowB = sheetB.getRow(i);
                System.out.println(String.format("第%d行", i));
                //4个一样的列比较
                if ((i < 17) ||
                        (21 < i && i < 36) ||
                        (89 < i && i < 101) ||
                        (105 < i && i < 117)){

                    Cell nameA = rowA.getCell(0,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell yiDongA = rowA.getCell(1,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell dianXingA = rowA.getCell(2,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell lianTongA = rowA.getCell(3,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell guangDianA = rowA.getCell(4,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell siJiaA = rowA.getCell(5,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    Cell nameB = rowB.getCell(0,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell yiDongB = rowB.getCell(1,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell dianXingB = rowB.getCell(2,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell lianTongB = rowB.getCell(3,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell guangDianB = rowB.getCell(4,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell siJiaB = rowB.getCell(5,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    if (nameA.getStringCellValue().equals(nameB.getStringCellValue())){

                        evaluateCell(workbookA,workbookB,yiDongA,yiDongB);
                        evaluateCell(workbookA,workbookB,dianXingA,dianXingB);
                        evaluateCell(workbookA,workbookB,lianTongA,lianTongB);
                        evaluateCell(workbookA,workbookB,guangDianA,guangDianB);
                        evaluateCell(workbookA,workbookB,siJiaA,siJiaB);

                    } else {
                        System.out.println("姓名不一致");
                    }

                } else if(38 < i && i < 53){

                    Cell nameA = rowA.getCell(0,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoGA = rowA.getCell(3,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoEA = rowA.getCell(4,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell quanYueGA = rowA.getCell(5,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell qianyueEA = rowA.getCell(6,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    Cell nameB = rowB.getCell(0,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoGB = rowB.getCell(3,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoEB = rowB.getCell(4,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell quanYueGB = rowB.getCell(5,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell qianyueEB = rowB.getCell(6,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    if (nameA.getStringCellValue().equals(nameB.getStringCellValue())){

                        evaluateCell(workbookA,workbookB,zhongBiaoGA,zhongBiaoGB);
                        evaluateCell(workbookA,workbookB,zhongBiaoEA,zhongBiaoEB);
                        evaluateCell(workbookA,workbookB,quanYueGA,quanYueGB);
                        evaluateCell(workbookA,workbookB,qianyueEA,qianyueEB);

                    } else {
                        System.out.println("姓名不一致");
                    }

                } else if(55 < i && i < 70){

                    Cell nameA = rowA.getCell(0,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoEA = rowA.getCell(4,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoFA = rowA.getCell(5,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    Cell nameB = rowB.getCell(0,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoEB = rowB.getCell(4,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoFB = rowB.getCell(5,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    if (nameA.getStringCellValue().equals(nameB.getStringCellValue())){

                        evaluateCell(workbookA,workbookB,zhongBiaoEA,zhongBiaoEB);
                        evaluateCell(workbookA,workbookB,zhongBiaoFA,zhongBiaoFB);

                    } else {
                        System.out.println("姓名不一致");
                    }

                } else if (73 < i && i < 85){

                    Cell nameA = rowA.getCell(0,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoGA = rowA.getCell(3,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoEA = rowA.getCell(4,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoJA = rowA.getCell(9,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoKA = rowA.getCell(10,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    Cell nameB = rowB.getCell(0,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoGB = rowB.getCell(3,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoEB = rowB.getCell(4,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoJB = rowB.getCell(9,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell zhongBiaoKB = rowB.getCell(10,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    if (nameA.getStringCellValue().equals(nameB.getStringCellValue())){

                        evaluateCell(workbookA,workbookB,zhongBiaoGA,zhongBiaoGB);
                        evaluateCell(workbookA,workbookB,zhongBiaoEA,zhongBiaoEB);
                        evaluateCell(workbookA,workbookB,zhongBiaoJA,zhongBiaoJB);
                        evaluateCell(workbookA,workbookB,zhongBiaoKA,zhongBiaoKB);

                    } else {
                        System.out.println("姓名不一致");
                    }

                } else {
//                    System.out.println("haha");
                }
            }
            // 将修改后的workbook写入文件
            try (FileOutputStream fos = new FileOutputStream(inputFilePathB)) {
                workbookB.write(fos);
            }

            // 输出提示信息
            System.out.println("处理完成，文件已保存到：" + inputFilePathB);
        }


    }

    public static void evaluateCell(Workbook workbookA,Workbook workbookB,Cell cellA,Cell cellB) {
        if (cellA != null && cellB != null) {
            // 创建一个公式评估器
            FormulaEvaluator evaluatorA = workbookA.getCreationHelper().createFormulaEvaluator();
            FormulaEvaluator evaluatorB = workbookB.getCreationHelper().createFormulaEvaluator();

            // 评估公式
            CellValue cellValueA = evaluatorA.evaluate(cellA);
            CellValue cellValueB = evaluatorB.evaluate(cellB);

            float numbA = caseCellNum(cellValueA);
            float numbB = caseCellNum(cellValueB);
            if (numbA > numbB || numbA == 9527f || numbB == 9527f) {
                setYellow(workbookB, cellB);
                System.out.println("颜色替换");
            }
        }
    }

    public static float caseCellNum(CellValue cellValue){
        float num = 9527f;
        if (cellValue == null){
            return 9527f;
        }
        switch (cellValue.getCellType()) {
            case NUMERIC:
                num = (float) cellValue.getNumberValue();
                break;
            case STRING:
                // 虽然 SUM 函数通常不会返回字符串，但处理这种情况以防万一
//                System.out.println("String result: " + cellValue.getStringValue());
                break;
        }
        return num;
    }

    public static void setYellow(Workbook workbook,Cell cell){
        // 获取单元格的当前样式
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();

        // 填充前景色为黄色
        XSSFColor yellowColor = new XSSFColor(new java.awt.Color(255, 255, 0), null);
        cellStyle.setFillForegroundColor(yellowColor);

        // 设置填充模式为填充
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置居中对齐（水平居中）
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        // 设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置边框（细线）
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        // 将CellStyle应用到单元格
        cell.setCellStyle(cellStyle);
    }


}
